package com.iplant.aps.serviceimpl.dao.sfc;

import com.iplant.aps.service.mesenum.*;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.sfc.SFCBOMTaskItem;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;

public class SFCBOMTaskItemDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(SFCBOMTaskItemDAO.class);

    private static SFCBOMTaskItemDAO Instance = null;

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, SFCBOMTaskItem wSFCBOMTaskItem, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wSFCBOMTaskItem == null)
                return 0;

            String wSQL = "";
            if (wSFCBOMTaskItem.getID() <= 0) {
                wSQL = StringUtils.Format("INSERT INTO {0}.sfc_bomtaskitem(SFCBOMTaskID,BOMID,BOMItemID,MaterialID,"
                                + "MaterialNumber,UnitID,Level,Responsibility,SapType,ReviewComments,Status,"
                                + "Remark,OperatorID,Operator,OperateTime,Quota,QualityLossBig,QualityLossSmall," +
                                "ShiftID,ReplaceType,OutsourceType,SupplierID,StockNum,BatchNumber,ProductUniqueCode) "
                                + "VALUES(:SFCBOMTaskID,:BOMID,:BOMItemID,:MaterialID,:MaterialNumber,"
                                + ":UnitID,:Level,:Responsibility,:SapType,:ReviewComments,:Status,:Remark,"
                                + ":OperatorID,:Operator,:OperateTime,:Quota,:QualityLossBig,:QualityLossSmall," +
                                ":ShiftID,:ReplaceType,:OutsourceType,:SupplierID,:StockNum,:BatchNumber,:ProductUniqueCode);",
                        wInstance.Result);
            } else {
                // 虚拟驳回时，修改操作时间
                Calendar wBaseTime = Calendar.getInstance();
                wBaseTime.set(2010, 0, 1, 0, 0, 0);
                if (wSFCBOMTaskItem.OperateTime.compareTo(wBaseTime) < 0 && wSFCBOMTaskItem.Status == 2) {
                    wSFCBOMTaskItem.OperateTime = Calendar.getInstance();
                    wSFCBOMTaskItem.OperatorID = wLoginUser.ID;
                    wSFCBOMTaskItem.Operator = wLoginUser.Name;
                }

                wSQL = StringUtils.Format(
                        "UPDATE {0}.sfc_bomtaskitem SET SFCBOMTaskID = :SFCBOMTaskID,"
                                + "BOMID = :BOMID,BOMItemID = :BOMItemID,MaterialID = :MaterialID,"
                                + "MaterialNumber = :MaterialNumber,UnitID = :UnitID,Level = :Level,"
                                + "Responsibility = :Responsibility,"
                                + "SapType = :SapType,ReviewComments=:ReviewComments,Status=:Status,Remark=:Remark,"
                                + "OperatorID=:OperatorID,Operator=:Operator,OperateTime=:OperateTime,Quota=:Quota,"
                                + "QualityLossBig=:QualityLossBig,QualityLossSmall=:QualityLossSmall,ShiftID=:ShiftID,"
                                + "ReplaceType=:ReplaceType,OutsourceType=:OutsourceType,SupplierID=:SupplierID," +
                                "StockNum=:StockNum,BatchNumber=:BatchNumber,ProductUniqueCode=:ProductUniqueCode WHERE" +
                                " ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wSFCBOMTaskItem.ID);
            wParamMap.put("SFCBOMTaskID", wSFCBOMTaskItem.SFCBOMTaskID);
            wParamMap.put("BOMID", wSFCBOMTaskItem.BOMID);
            wParamMap.put("BOMItemID", wSFCBOMTaskItem.BOMItemID);
            wParamMap.put("MaterialID", wSFCBOMTaskItem.MaterialID);
            wParamMap.put("MaterialNumber", wSFCBOMTaskItem.MaterialNumber);
            wParamMap.put("UnitID", wSFCBOMTaskItem.UnitID);
            wParamMap.put("Level", wSFCBOMTaskItem.Level);
            wParamMap.put("Responsibility", wSFCBOMTaskItem.Responsibility);
            wParamMap.put("SapType", wSFCBOMTaskItem.SapType);
            wParamMap.put("ReviewComments", wSFCBOMTaskItem.ReviewComments);
            wParamMap.put("Status", wSFCBOMTaskItem.Status);
            wParamMap.put("Remark", wSFCBOMTaskItem.Remark);
            wParamMap.put("OperatorID", wSFCBOMTaskItem.OperatorID);
            wParamMap.put("Operator", wSFCBOMTaskItem.Operator);
            wParamMap.put("OperateTime", wSFCBOMTaskItem.OperateTime);
            wParamMap.put("Quota", wSFCBOMTaskItem.Quota);
            wParamMap.put("QualityLossBig", wSFCBOMTaskItem.QualityLossBig);
            wParamMap.put("QualityLossSmall", wSFCBOMTaskItem.QualityLossSmall);
            wParamMap.put("ShiftID", wSFCBOMTaskItem.ShiftID);
            wParamMap.put("ReplaceType", wSFCBOMTaskItem.ReplaceType);
            wParamMap.put("OutsourceType", wSFCBOMTaskItem.OutsourceType);
            wParamMap.put("SupplierID", wSFCBOMTaskItem.SupplierID);
            wParamMap.put("StockNum", wSFCBOMTaskItem.StockNum);
            wParamMap.put("BatchNumber", wSFCBOMTaskItem.BatchNumber);
            wParamMap.put("ProductUniqueCode", wSFCBOMTaskItem.ProductUniqueCode);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wSFCBOMTaskItem.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wSFCBOMTaskItem.setID(wResult);
            } else {
                wResult = wSFCBOMTaskItem.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<SFCBOMTaskItem> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (SFCBOMTaskItem wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.sfc_bomtaskitem WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public SFCBOMTaskItem SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        SFCBOMTaskItem wResult = new SFCBOMTaskItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<SFCBOMTaskItem> wList = SelectList(wLoginUser, wID, -1, wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<SFCBOMTaskItem> SelectList(BMSEmployee wLoginUser, int wID, int wSFCBOMTaskID,
                                           OutResult<Integer> wErrorCode) {
        List<SFCBOMTaskItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            ServiceResult<String> wInstance1 = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance1.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = StringUtils.Format(
                    "SELECT t1.*,t2.MaterialName,t2.MaterialNo,t3.Name as UnitText,t4.SupplierName,t4.SupplierCode "
                            + "FROM {0}.sfc_bomtaskitem t1 " +
                            " left join {0}.mss_material t2 on t1.MaterialID=t2.ID "
                            + " left join {0}.cfg_unit t3 on t1.UnitID=t3.ID "
                            + " left join {0}.scm_supplier t4 on t1.SupplierID=t4.ID "
                            + " WHERE 1=1 "
                            + "and ( :wID <= 0 or :wID = t1.ID ) "
                            + "and ( :wSFCBOMTaskID <= 0 or :wSFCBOMTaskID = t1.SFCBOMTaskID );",
                    wInstance.Result, wInstance1.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wSFCBOMTaskID", wSFCBOMTaskID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SetValue(wResultList, wQueryResult);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    private void SetValue(List<SFCBOMTaskItem> wResultList, List<Map<String, Object>> wQueryResult) {
        try {
            for (Map<String, Object> wReader : wQueryResult) {
                SFCBOMTaskItem wItem = new SFCBOMTaskItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.SFCBOMTaskID = StringUtils.parseInt(wReader.get("SFCBOMTaskID"));
                wItem.BOMID = StringUtils.parseInt(wReader.get("BOMID"));
                wItem.BOMItemID = StringUtils.parseInt(wReader.get("BOMItemID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.Level = StringUtils.parseInt(wReader.get("Level"));
                wItem.Responsibility = StringUtils.parseInt(wReader.get("Responsibility"));
                wItem.SapType = StringUtils.parseInt(wReader.get("SapType"));
                wItem.ReviewComments = StringUtils.parseInt(wReader.get("ReviewComments"));

                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.UnitText = StringUtils.parseString(wReader.get("UnitText"));

                wItem.SupplierID = StringUtils.parseInt(wReader.get("SupplierID"));
                wItem.SupplierName = StringUtils.parseString(wReader.get("SupplierName"));
                wItem.SupplierCode = StringUtils.parseString(wReader.get("SupplierCode"));

                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.QualityLossBig = StringUtils.parseString(wReader.get("QualityLossBig"));
                wItem.QualityLossSmall = StringUtils.parseString(wReader.get("QualityLossSmall"));
                wItem.OperatorID = StringUtils.parseInt(wReader.get("OperatorID"));
                wItem.Operator = StringUtils.parseString(wReader.get("Operator"));
                wItem.OperateTime = StringUtils.parseCalendar(wReader.get("OperateTime"));
                wItem.Quota = StringUtils.parseDouble(wReader.get("Quota"));
                wItem.StockNum = StringUtils.parseDouble(wReader.get("StockNum"));
                wItem.ShiftID = StringUtils.parseString(wReader.get("ShiftID"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.OutsourceType = StringUtils.parseInt(wReader.get("OutsourceType"));
                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.ProductUniqueCode = StringUtils.parseString(wReader.get("ProductUniqueCode"));

                wItem.ReplaceTypeText = SFCReplaceType.getEnumType(wItem.ReplaceType).getLable();
                wItem.OutsourceTypeText = SFCOutSourceType.getEnumType(wItem.OutsourceType).getLable();

                wItem.LevelName = NCRLevel.getEnumType(wItem.Level).getLable();
                wItem.ResponsibilityName = SFCBOMTaskResponsibility.getEnumType(wItem.Responsibility).getLable();
                wItem.ReviewCommentsName = SFCBOMTaskReviewComments.getEnumType(wItem.ReviewComments).getLable();

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 条件查询集合
     */
    public List<SFCBOMTaskItem> SelectList(BMSEmployee wLoginUser, List<Integer> wTaskIDList,
                                           OutResult<Integer> wErrorCode) {
        List<SFCBOMTaskItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (wTaskIDList == null || wTaskIDList.size() <= 0) {
                return wResultList;
            }

            String wSQL = StringUtils.Format(
                    "SELECT t1.*,t2.MaterialName,t2.MaterialNo,t3.Name as UnitText,t4.SupplierName,t4.SupplierCode "
                            + "FROM {0}.sfc_bomtaskitem t1 " +
                            " left join {0}.mss_material t2 on t1.MaterialID=t2.ID "
                            + " left join {0}.cfg_unit t3 on t1.UnitID=t3.ID "
                            + " left join {0}.scm_supplier t4 on t1.SupplierID=t4.ID "
                            + " WHERE 1=1 "
                            + "and ( t1.SFCBOMTaskID in ({1}));",
                    wInstance.Result, StringUtils.Join(",", wTaskIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wSFCBOMTaskID", StringUtils.Join(",", wTaskIDList));

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            this.SetValue(wResultList, wQueryResult);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    private SFCBOMTaskItemDAO() {
        super();
    }

    public static SFCBOMTaskItemDAO getInstance() {
        if (Instance == null)
            Instance = new SFCBOMTaskItemDAO();
        return Instance;
    }

    /**
     * 查询数量
     */
    public double SelectUsedNumber(BMSEmployee wLoginUser, int orderID, int partID, int partPointID, int materialID,
                                   OutResult<Integer> wErrorCode) {
        double wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT sum(t1.MaterialNumber) Number FROM "
                            + "{0}.sfc_bomtaskitem t1,{0}.sfc_bomtask t2 "
                            + "where t1.SFCBOMTaskID = t2.ID and t1.MaterialID=:MaterialID and t2.OrderID=:OrderID " +
                            "and t1.Status !=2 "
                            + "and t2.PartID=:PartID and t2.PartPointID=:PartPointID and t2.StatusText != ''待作业人员 " +
                            "发起评审'' and t2" +
                            ".Status not in (0,21,22," +
                            "23);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("MaterialID", materialID);
            wParamMap.put("OrderID", orderID);
            wParamMap.put("PartID", partID);
            wParamMap.put("PartPointID", partPointID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseDouble(wReader.get("Number"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<Integer> GetQuotaZeroList(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.sfc_bomtaskitem where Quota=0 and status=0;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wID = StringUtils.parseInt(wReader.get("ID"));
                wResult.add(wID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public double GetQuota(BMSEmployee wLoginUser, int partID, int partPointID, int materialID,
                           OutResult<Integer> wErrorCode) {
        double wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT distinct MaterialNumber FROM {0}.mss_bomitem "
                            + "where ReplaceType=2 and MaterialID=:MaterialID and PlaceID=:PlaceID and " +
                            "PartPointID=:PartPointID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("MaterialID", materialID);
            wParamMap.put("PlaceID", partID);
            wParamMap.put("PartPointID", partPointID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseDouble(wReader.get("MaterialNumber"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
